package com.dldata.devtools30.utils.dbtable.imp;


import com.dldata.devtools30.entity.DevtModelMetadataFieldEntity;
import com.dldata.devtools30.entity.DevtModelMetadataTableEntity;
import com.dldata.devtools30.repository.common.DlBaseJdbcTemplate;
import com.dldata.devtools30.utils.DBTypeUtil;
import com.dldata.devtools30.utils.StringUtil;
import com.dldata.devtools30.utils.dbtable.DbTableManager;
import com.dldata.devtools30.utils.dbtable.DbTableServiceIV2;
import com.dldata.devtools30.utils.jdbc.JdbcDataAcess;
import lombok.extern.slf4j.Slf4j;
import org.springframework.jdbc.datasource.DataSourceUtils;

import javax.sql.DataSource;
import java.sql.*;
import java.util.LinkedList;
import java.util.List;
import java.util.UUID;

@Slf4j
/**
 * Created by 呵呵 on 2018-01-02.
 */
public abstract class DbTableServiceIAbstract implements DbTableServiceIV2 {

    private DlBaseJdbcTemplate dlBaseJdbcTemplate;

    private JdbcDataAcess jdbcDataAcess;

    public DbTableServiceIAbstract(DlBaseJdbcTemplate dlBaseJdbcTemplate,JdbcDataAcess jdbcDataAcess){
        this.dlBaseJdbcTemplate = dlBaseJdbcTemplate;
        this.jdbcDataAcess=jdbcDataAcess;
    }



    @Override
    public List<DevtModelMetadataFieldEntity> queryTableFields(DevtModelMetadataTableEntity table) {
        return getTableInfo(table.getCode());
    }

    /**
     * 根据数据库的连接参数，获取指定表的基本信息：字段名、字段类型、字段注释
     * @param table 表名
     * @return Map集合
     */
    private List<DevtModelMetadataFieldEntity> getTableInfo(String table){

        List<DevtModelMetadataFieldEntity> sysCrfFormFieldEntityList=new LinkedList<>();
        Connection conn = null;
        DatabaseMetaData dbmd = null;
        DataSource dataSource =null;
        try {
            dataSource = dlBaseJdbcTemplate.getDataSource();
            conn =jdbcDataAcess!=null?jdbcDataAcess.getConnection():DataSourceUtils.getConnection(dataSource);
            dbmd = conn.getMetaData();
            ResultSet resultSet = dbmd.getTables(conn.getCatalog(), "%", table, new String[] { "TABLE" });

            while (resultSet.next()) {
                String tableName = resultSet.getString("TABLE_NAME");
                ResultSet rsField = conn.getMetaData().getColumns(conn.getCatalog(), getSchema(conn), tableName.toUpperCase(),null);

                if (!rsField.next()) {
                    rsField = conn.getMetaData().getColumns(conn.getCatalog(), getSchema(conn), tableName, null);
                }
                do {
                    DevtModelMetadataFieldEntity sysCrfFormFieldEntity=new DevtModelMetadataFieldEntity();
                    sysCrfFormFieldEntity.setId(UUID.randomUUID().toString());
//                    sysCrfFormFieldEntity.setDataSql("");
                    sysCrfFormFieldEntity.setFormId("");
//                    sysCrfFormFieldEntity.setCancel(false);
                    sysCrfFormFieldEntity.setXh(sysCrfFormFieldEntityList.size()+1);
                    sysCrfFormFieldEntity.setFieldCode(rsField.getString("COLUMN_NAME"));
                    sysCrfFormFieldEntity.setFieldName(rsField.getString("COLUMN_NAME"));
                    sysCrfFormFieldEntity.setBz(StringUtil.isEmptyOrLength0(rsField.getString("REMARKS"))?rsField.getString("COLUMN_NAME"):rsField.getString("REMARKS"));
//                    sysCrfFormFieldEntity.setFieldZhname(StringUtil.isEmptyOrLength0(rsField.getString("REMARKS"))?rsField.getString("COLUMN_NAME"):rsField.getString("REMARKS"));
                    sysCrfFormFieldEntity.setPkey(sysCrfFormFieldEntity.getFieldName().equalsIgnoreCase("ID")?1:0);
//                    sysCrfFormFieldEntity.setControlType(V_SysFormFieldController.ControlType._文本控件.name().replace("_",""));
                    int DECIMAL_DIGITS = rsField.getInt("DECIMAL_DIGITS");
                    int COLUMN_SIZE = rsField.getInt("COLUMN_SIZE");
                    sysCrfFormFieldEntity.setTypeLength(COLUMN_SIZE);
                    sysCrfFormFieldEntity.setTypeZeroLength(DECIMAL_DIGITS);
                    sysCrfFormFieldEntityList.add(sysCrfFormFieldEntity);
                    int DATA_TYPE = rsField.getInt("DATA_TYPE");
                    String TYPE_NAME = rsField.getString("TYPE_NAME");
                    switch (DATA_TYPE){
                        case Types.DATE:{
                            sysCrfFormFieldEntity.setFieldType(DbTableManager.FIELDTYPE.DATE.getCode());
//                            sysCrfFormFieldEntity.setControlType(V_SysFormFieldController.ControlType._日期控件.name().replace("_", ""));
                            break;
                        }
                        case Types.TIME_WITH_TIMEZONE:
                        case Types.TIME:{
                            sysCrfFormFieldEntity.setFieldType(DbTableManager.FIELDTYPE.DATETIME.getCode());
//                            sysCrfFormFieldEntity.setControlType(V_SysFormFieldController.ControlType._日期时间控件.name().replace("_",""));
                            break;
                        }
                        case Types.TIMESTAMP_WITH_TIMEZONE:
                        case Types.TIMESTAMP:{
                            sysCrfFormFieldEntity.setFieldType(DbTableManager.FIELDTYPE.DATETIME.getCode());
//                            sysCrfFormFieldEntity.setControlType(V_SysFormFieldController.ControlType._日期时间控件.name().replace("_",""));
                            break;
                        }
                        case Types.BOOLEAN:
                        case Types.BIT:{
                            sysCrfFormFieldEntity.setFieldType(DbTableManager.FIELDTYPE.BOOLEAN.getCode());
                            break;
                        }
                        case Types.CHAR:
                        case Types.BIGINT:
                        case Types.TINYINT:
                        case Types.SMALLINT:
                        case Types.INTEGER:{
                            if(DECIMAL_DIGITS==0&&COLUMN_SIZE<=Integer.valueOf(Integer.MAX_VALUE).toString().length()){
                                sysCrfFormFieldEntity.setFieldType(DbTableManager.FIELDTYPE.INTEGER.getCode());
                            }else{
                                sysCrfFormFieldEntity.setFieldType(DbTableManager.FIELDTYPE.DECEMEMOL.getCode());
                            }
                            break;
                        }
                        case Types.FLOAT:
                        case Types.NUMERIC:
                        case Types.DECIMAL:
                        case Types.DOUBLE:{
                            if(DECIMAL_DIGITS==0&&COLUMN_SIZE<=Integer.valueOf(Integer.MAX_VALUE).toString().length()){
                                sysCrfFormFieldEntity.setFieldType(DbTableManager.FIELDTYPE.INTEGER.getCode());
                            }else{
                                sysCrfFormFieldEntity.setFieldType(DbTableManager.FIELDTYPE.DECEMEMOL.getCode());
                            }
                            break;
                        }
                        case Types.BLOB:
                        case Types.LONGVARBINARY:
                        case Types.LONGVARCHAR:
                        case Types.LONGNVARCHAR:
                        case Types.NCLOB:
                        case Types.CLOB:{
                            sysCrfFormFieldEntity.setFieldType(DbTableManager.FIELDTYPE.STRING.getCode());
                            sysCrfFormFieldEntity.setTypeLength(Integer.MAX_VALUE);
                            break;
                        }
                        case Types.VARBINARY:
                        case Types.ARRAY:
                        case Types.BINARY:{
                            sysCrfFormFieldEntity.setFieldType(DbTableManager.FIELDTYPE.STRING.getCode());
                            break;
                        }
                        case Types.VARCHAR:
                        case Types.NCHAR:
                        case Types.NVARCHAR:
                        default:{
                            sysCrfFormFieldEntity.setFieldType(DbTableManager.FIELDTYPE.STRING.getCode());
                            break;
                        }
                    }

                }while (rsField.next());
            }
        } catch (Exception e) {
            log.error(e.getMessage(),e);
        }finally{
            if(jdbcDataAcess!=null){
                jdbcDataAcess.closeConnection(conn,null,null);
            }else {
                DataSourceUtils.releaseConnection(conn,dataSource);
            }
        }

        if(sysCrfFormFieldEntityList.size()==0){
            StringBuilder testSql=new StringBuilder();
            testSql.append(" select * from  ");
            testSql.append(table);
            testSql.append(" where 1=2 ");
            try {
                dataSource = dlBaseJdbcTemplate.getDataSource();
                conn = jdbcDataAcess != null ? jdbcDataAcess.getConnection() : DataSourceUtils.getConnection(dataSource);
                ResultSet resultSet=conn.createStatement().executeQuery(testSql.toString());
                ResultSetMetaData resultSetMetaData=resultSet.getMetaData();
                log.info("resultSetMetaData.getColumnCount: "+resultSetMetaData.getColumnCount());
                for (int i = 1; i <= resultSetMetaData.getColumnCount(); i++) {
                    DevtModelMetadataFieldEntity sysCrfFormFieldEntity=new DevtModelMetadataFieldEntity();
                    sysCrfFormFieldEntity.setId(UUID.randomUUID().toString());
//                    sysCrfFormFieldEntity.setDataSql("");
                    sysCrfFormFieldEntity.setFormId("");
//                    sysCrfFormFieldEntity.setCancel(false);
                    sysCrfFormFieldEntity.setXh(sysCrfFormFieldEntityList.size()+1);
                    sysCrfFormFieldEntity.setFieldCode(resultSetMetaData.getColumnName(i));
                    sysCrfFormFieldEntity.setFieldName(resultSetMetaData.getColumnName(i));
                    sysCrfFormFieldEntity.setBz(resultSetMetaData.getColumnName(i));
//                    sysCrfFormFieldEntity.setFieldZhname(resultSetMetaData.getColumnName(i));
                    sysCrfFormFieldEntity.setPkey(sysCrfFormFieldEntity.getFieldName().equalsIgnoreCase("ID")?1:0);
//                    sysCrfFormFieldEntity.setControlType(V_SysFormFieldController.ControlType._文本控件.name().replace("_",""));
                    int DECIMAL_DIGITS = resultSetMetaData.getScale(i);
                    int COLUMN_SIZE = resultSetMetaData.getColumnDisplaySize(i);
//                    resultSetMetaData.getPrecision();
                    sysCrfFormFieldEntity.setTypeLength(COLUMN_SIZE);
                    sysCrfFormFieldEntity.setTypeZeroLength(DECIMAL_DIGITS);
                    sysCrfFormFieldEntityList.add(sysCrfFormFieldEntity);

                    int DATA_TYPE = resultSetMetaData.getColumnType(i);
                    String TYPE_NAME = resultSetMetaData.getColumnTypeName(i);
                    switch (DATA_TYPE){
                        case Types.DATE:{
                            sysCrfFormFieldEntity.setFieldType(DbTableManager.FIELDTYPE.DATE.getCode());
//                            sysCrfFormFieldEntity.setControlType(V_SysFormFieldController.ControlType._日期控件.name().replace("_", ""));
                            break;
                        }
                        case Types.TIME_WITH_TIMEZONE:
                        case Types.TIME:{
                            sysCrfFormFieldEntity.setFieldType(DbTableManager.FIELDTYPE.DATETIME.getCode());
//                            sysCrfFormFieldEntity.setControlType(V_SysFormFieldController.ControlType._日期时间控件.name().replace("_",""));
                            break;
                        }
                        case Types.TIMESTAMP_WITH_TIMEZONE:
                        case Types.TIMESTAMP:{
                            sysCrfFormFieldEntity.setFieldType(DbTableManager.FIELDTYPE.DATETIME.getCode());
//                            sysCrfFormFieldEntity.setControlType(V_SysFormFieldController.ControlType._日期时间控件.name().replace("_",""));
                            break;
                        }
                        case Types.BOOLEAN:
                        case Types.BIT:{
                            sysCrfFormFieldEntity.setFieldType(DbTableManager.FIELDTYPE.BOOLEAN.getCode());
                            break;
                        }
                        case Types.CHAR:
                        case Types.BIGINT:
                        case Types.TINYINT:
                        case Types.SMALLINT:
                        case Types.INTEGER:{
                            if(DECIMAL_DIGITS==0&&COLUMN_SIZE<=Integer.valueOf(Integer.MAX_VALUE).toString().length()){
                                sysCrfFormFieldEntity.setFieldType(DbTableManager.FIELDTYPE.INTEGER.getCode());
                            }else{
                                sysCrfFormFieldEntity.setFieldType(DbTableManager.FIELDTYPE.DECEMEMOL.getCode());
                            }
                            break;
                        }
                        case Types.FLOAT:
                        case Types.NUMERIC:
                        case Types.DECIMAL:
                        case Types.DOUBLE:{
                            if(DECIMAL_DIGITS==0&&COLUMN_SIZE<=Integer.valueOf(Integer.MAX_VALUE).toString().length()){
                                sysCrfFormFieldEntity.setFieldType(DbTableManager.FIELDTYPE.INTEGER.getCode());
                            }else{
                                sysCrfFormFieldEntity.setFieldType(DbTableManager.FIELDTYPE.DECEMEMOL.getCode());
                            }
                            break;
                        }
                        case Types.BLOB:
                        case Types.LONGVARBINARY:
                        case Types.LONGVARCHAR:
                        case Types.LONGNVARCHAR:
                        case Types.NCLOB:
                        case Types.CLOB:{
                            sysCrfFormFieldEntity.setFieldType(DbTableManager.FIELDTYPE.STRING.getCode());
                            sysCrfFormFieldEntity.setTypeLength(Integer.MAX_VALUE);
                            break;
                        }
                        case Types.VARBINARY:
                        case Types.ARRAY:
                        case Types.BINARY:{
                            sysCrfFormFieldEntity.setFieldType(DbTableManager.FIELDTYPE.STRING.getCode());
                            break;
                        }
                        case Types.VARCHAR:
                        case Types.NCHAR:
                        case Types.NVARCHAR:
                        default:{
                            sysCrfFormFieldEntity.setFieldType(DbTableManager.FIELDTYPE.STRING.getCode());
                            break;
                        }
                    }
                }

            } catch (Exception e) {
                log.error(e.getMessage(),e);
            }finally{
                if(jdbcDataAcess!=null){
                    jdbcDataAcess.closeConnection(conn,null,null);
                }else {
                    DataSourceUtils.releaseConnection(conn,dataSource);
                }
            }



        }

        return sysCrfFormFieldEntityList;
    }

    /**
     * 其他数据库不需要这个方法 oracle和db2需要
     * @param conn
     * @return
     * @throws Exception
     */
    private  String getSchema(Connection conn) throws Exception {
        String schema=null;
        schema = conn.getMetaData().getUserName();
        if ((schema == null) || (schema.length() == 0)) {
            throw new Exception("ORACLE数据库模式不允许为空");
        }
        DBTypeUtil.DBTYPE dbtype= DBTypeUtil.getDbtype(dlBaseJdbcTemplate.getDataSource());
        if (DBTypeUtil.DBTYPE.mysql.equals(dbtype)|| DBTypeUtil.DBTYPE.sqlserver.equals(dbtype)) {
            return "%";
        }
        return schema.toUpperCase().toString();
    }

    private static String changeDbType(String dbType) {
        dbType = dbType.toUpperCase();
        switch(dbType){
            case "VARCHAR":
            case "VARCHAR2":
            case "CHAR":
                return "1";
            case "NUMBER":
            case "DECIMAL":
                return "4";
            case "INT":
            case "SMALLINT":
            case "INTEGER":
                return "2";
            case "BIGINT":
                return "6";
            case "DATETIME":
            case "TIMESTAMP":
            case "DATE":
                return "7";
            default:
                return "1";
        }
    }
}
